MySQL (a trademark of MySQL AB Limited Company or its successors) is a popular open source database management system. Natively, MySQL may be configured to replicate data from a master node to a slave node asynchronously or semi-synchronously, but not synchronously.
In asynchronous data replication, data is replicated from a master node to a slave node independent of when transactions are committed at the master node. Thus, transactions may be committed at the master node without consideration to when the transactions will be replicated to the slave node. Asynchronous replication therefore enables transactions to be committed relatively quickly at the master node, but if the master node becomes inoperable, there is no guarantee that a transaction committed at the master node has been replicated to the slave node. In asynchronous replication, the data stored on the slave node may not be current with data stored on the master node. As a result, read operations performed on the slave node may read out of date data. Further, if the master node crashes, then the slave node may not have the most recent set of data, resulting in data loss.
In semi-synchronous replication, a transaction is only committed at the master node when the master node receives acknowledgement that the slave node has received a copy of the transaction. Thus, when a transaction is committed at the master node, there is a guarantee that the slave node has at least received the transaction.
In synchronous replication, a transaction is only committed at the master node when the master node receives acknowledgement that the slave node has committed the transaction. Thus, when a transaction is committed at the master node, there is a guarantee that the slave node has also committed the transaction. Synchronous replication therefore requires additional time to commit a transaction at the master node than compared to asynchronous replication; however, if the master node becomes inoperable, there is a guarantee that the state of the database maintained by the slave node is consistent with the state of the database at the master node prior to the master node becoming inoperable.
MySQL may be configured to employ a third party library to provide additional functionality to a MySQL installation. For example, MySQL may be used in conjunction with a third party synchronous replication library, such as Galera. A MySQL server integrated with the Galera library enables a plurality of MySQL servers to interact with each other in a master-slave synchronous replication relationship.
In a Galera-based master-slave MySQL synchronous replication cluster, one MySQL server functions as a master and one or more MySQL servers function as a slave. The MySQL master server can handle both read and write requests while a MySQL slave server can handle only read requests. MySQL clients may only send write transactions to the MySQL master server but may send read transactions to either the MySQL master server or any MySQL slave servers.
A write set is prepared at the master for each MySQL write transaction initiated at the master. A write set is a set of information that may be used to perform the write operations that are specified by the requested MySQL write transaction. The write set is replicated from the master to each slave and is used by each slave to perform the write operations that are specified by the requested write transaction at the slave. Each slave uses write sets to commit the write transaction.
In a Galera-based master-slave MySQL synchronous replication cluster, write transactions received at a MySQL master server are replicated synchronously to each MySQL slave server. When a MySQL slave server receives a particular read query, to ensure data consistency, the MySQL slave server waits for all transactions, received by the MySQL slave server prior to the particular read query, to be committed prior to processing the particular read query. Unfortunately, if the MySQL master server receives a large volume of write transactions, then the performance of performing read queries at each MySQL slave server is poor.
Discussion in this section is meant to provide an understanding of prior approaches to the reader as they relate to embodiments of the invention. However, the disadvantages of prior approaches discussed in this section are not meant to be an admission that such disadvantages were publically known. Consequently, recognition of the disadvantages of the prior art discussed herein is not meant to be construed as being within the prior art simply by virtue of its inclusion in this section alone.